SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO



ALTER    function f_get_tt_bill()
returns @tt_bill table
( 	[tid]int,
	[bid]int,
	[bstate]int,
	[bdate]datetime,
	[btype]int,
	[bcode]varchar(32),
	[bno]int,
	[depotid] int,
	[productid] int,
	[bquantity] decimal(18,4),
	[bprice] decimal(18,8),
	[amount] decimal(18,2),	
	[assqty] decimal(18,4),
	[unitid]int,
	[unitrate]decimal(18,4),
	[depotname]varchar(62)
)

as
begin
	insert into @tt_bill  
	SELECT MIN(tb.tid) AS tid, tb.bid,bstate, tb.bdate, tb.btype,  
	      MIN(tb.bcode) AS bcode, MIN(tb.bno) AS bno, tb.depotid, tb.productid, SUM(tb.bquantity) 
	      AS bquantity,case when SUM(tb.bquantity) <> 0 then SUM(tb.amount) / SUM(tb.bquantity)else min(tb.price) end AS bprice ,sum(tb.amount) as amount,
	      SUM(tb.assqty) AS assqty, MIN(tb.unitid) AS unitid, MIN(tb.unitrate) AS unitrate, td.name as depotname
	FROM tt_bill tb left join td_depot td on tb.depotid = td.depotid
	where btype < 0
	GROUP BY tb.depotid, tb.productid,bstate, tb.bdate, tb.bid, tb.btype, td.name
	ORDER BY tb.tid
	return 
end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

